<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="/common/jsp/taglibs.jsp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <title>SQL生成器</title>
    <%@ include file="/common/jsp/jscss.jsp" %>
    <%--<script src="${ctx}/common/sysProject/sqlCreated/sqlCreatedPanel.js" type="text/javascript"></script>--%>
    <%--<script src="js/sqlCreatedPanel.js" type="text/javascript"></script>--%>

    <script type="text/javascript">
        $(function () {
            setButtonDisabled(true);
            var driver = $.cookie('sillyhat_sql_created_cookie_driver');
            var url = $.cookie('sillyhat_sql_created_cookie_url');
            var username = $.cookie('sillyhat_sql_created_cookie_username');
            var password = $.cookie('sillyhat_sql_created_cookie_password');
            $("#driver").val(driver);
            $("#url").val(url);
            $("#username").val(username);
            $("#password").val(password);
            refreshCookie(driver,url,username,password);
        });

        function testConnectionDatabase(){
            $.ajax({
                url : "${ctx}/sillyhat/admin/sqlcreated/testConnectionDatabase",
                async : false,
                error : function(data) {
                    alert("网络错误");
                },
                data : {
                    "driver" : $("#driver").val(),
                    "url" : $("#url").val(),
                    "username" : $("#username").val(),
                    "password" : $("#password").val()
                },
                type : "get",
                dataType: "json",
                success : function(data){
                    if(data != null){
                        if(data.judge){
                            alert("连接成功");
                        }else{
                            alert("连接失败");
                        }
                    }
                }
            });
        }
        function clearTable(){
            $("#showTableContentId tbody").html("<tr id=\"trSearchTableId\"></tr>");
        }
        function searchTable(){
            if($("#tableName").val() == ""){
                alert("需要输入表名");
                return;
            }
            $("#fieldTableName").val($("#tableName").val())
            setButtonDisabled(true);
            clearTable();
            $.ajax({
                url : "${ctx}/sillyhat/admin/sqlcreated/searchTable",
                async : false,
                error : function(data) {
                    alert("网络错误");
                },
                data : {
                    "driver" : $("#driver").val(),
                    "url" : $("#url").val(),
                    "username" : $("#username").val(),
                    "password" : $("#password").val(),
                    "tableName" : $("#tableName").val()
                },
                type : "get",
                dataType: "json",
                success : function(data){
                    if(data != null){
                        if(data.length > 0){
                            $("#fieldNumber").val(data.length)
                            setButtonDisabled(false);
                            $.each(data, function(idx, obj){
                                addRow(idx,obj);
                            });
//                            for (var i = 0; i < data.length; i++){
//                                addRow(data[i]);
//                            }
                        }else{
                            alert("查询失败失败，数据库连接异常或查询表结构失败");
                        }
                    }
                }
            });
        }

        function addRow(rowIndex,obj){
            var isKey = "";
            if("1" == obj.IS_KEY){
                isKey = "主键";
            }
            var tr =
                "<tr id=\"createdSql_"+(rowIndex + 1)+"\" align='center'>" +
                "	<td>" + (rowIndex + 1) + "</td>" +
                "   <td>" + obj.COLUMN_NAME + "</td>" + "<input type=\"hidden\" id=\"fieldName_" + rowIndex + "\" value=\"" + obj.COLUMN_NAME + "\">" +
                "   <td>" + obj.DATA_TYPE + "</td>" + "<input type=\"hidden\" id=\"fieldType_" + rowIndex + "\" value=\"" + obj.DATA_TYPE + "\">" +
                "   <td>" + obj.COMMENTS + "</td>" + "<input type=\"hidden\" id=\"entityField_" + rowIndex + "\" value=\"" + obj.ENTITY_FIELD + "\">" +
                "   <td>" + isKey + "</td>" +
                "</tr>";
            $("#trSearchTableId").before(tr);
        }
        function saveCookie(){
            refreshCookie($("#driver").val(),$("#url").val(),$("#username").val(),$("#password").val());
            alert("保存成功");
        }


        function createdOracleSql(){
            var resultSql =
                getSelectAllSql("oracle") + "<br/><br/><br/><br/><br/>" +
                getSelectOneSql("oracle") + "<br/><br/><br/><br/><br/>" +
                getSelectPageSql("oracle") + "<br/><br/><br/><br/><br/>" +
                getSelectPageCountSql("oracle") + "<br/><br/><br/><br/><br/>" +
                getUpdateSql("oracle") + "<br/><br/><br/><br/><br/>" +
                getInsertSql("oracle") + "<br/><br/><br/><br/><br/>" +
                getDeleteSql("oracle");
            $("#resultSql").html(resultSql);
        }

        function createdMybatisSql(){
            var resultSql =
                getSelectAllSql("mybatis") + "<br/><br/><br/><br/><br/>" +
                getSelectOneSql("mybatis") + "<br/><br/><br/><br/><br/>" +
                getSelectPageSql("mybatis") + "<br/><br/><br/><br/><br/>" +
                getSelectPageCountSql("mybatis") + "<br/><br/><br/><br/><br/>" +
                getUpdateSql("mybatis") + "<br/><br/><br/><br/><br/>" +
                getInsertSql("mybatis") + "<br/><br/><br/><br/><br/>" +
                getDeleteSql("mybatis");
            $("#resultSql").html(resultSql);
        }

        function setButtonDisabled(btnDisabled) {
            $("#btnCreatedOracleSqlId").attr('disabled',btnDisabled);//设置disabled属性为false，按钮可用
            $("#btnCreatedMybatisSqlId").attr('disabled',btnDisabled);//设置disabled属性为true，按钮不可用
        }

        function refreshCookie(driver,url,username,password){
            $.cookie('sillyhat_sql_created_cookie_driver', driver, { expires: 30 });
            $.cookie('sillyhat_sql_created_cookie_url', url, { expires: 30 });
            $.cookie('sillyhat_sql_created_cookie_username',username, { expires: 30 });
            $.cookie('sillyhat_sql_created_cookie_password',password, { expires: 30 });
        }

        /**
         * 查询全部
         */
        function getSelectAllSql(type){
            var sql = "--查询全部<br>";
            if("oracle" == type){
                sql += "select ";
                for (var i = 0; i < $("#fieldNumber").val(); i++){
                    sql += $("#fieldName_" + i).val() + ",";
                }
                sql = sql.substring(0,sql.length - 1);
                sql += " from " + $("#fieldTableName").val() + ";";
                return sql;
            }else if("mybatis" == type){

                return sql;
            }else{

                return sql;
            }
        }

        /**
         * 查询一个
         */
        function getSelectOneSql(type){
            var sql = "--根据主键查询<br>";
            if("oracle" == type){
                sql += "select ";
                for (var i = 0; i < $("#fieldNumber").val(); i++){
                    sql += $("#fieldName_" + i).val() + ",";
                }
                sql = sql.substring(0,sql.length - 1);
                sql += " from " + $("#fieldTableName").val() + " where " + $("#fieldName_0").val() + " = '';";
                return sql;
            }else if("mybatis" == type){

                return sql;
            }else{

                return sql;
            }
        }

        /**
         * 分页查询
         */
        function getSelectPageSql(type){
            var sql = "--分页查询<br>";
            if("oracle" == type){
                sql += "select ";
                for (var i = 0; i < $("#fieldNumber").val(); i++){
                    sql += $("#fieldName_" + i).val() + ",";
                }
                sql = sql.substring(0,sql.length - 1);
                sql += "  from " + $("#fieldTableName").val() + "<br>" +
                    " where rowid in (select rid                             <br>" +
                    "                   from (select rownum rn, rid          <br>" +
                    "                           from (select rowid rid, id   <br>" +
                    "                                   from " + $("#fieldTableName").val() + "        <br>" +
                    "                                  where 1 = 1           <br>" +
                    "                                  order by order_by desc)<br>" +
                    "                          where rownum <= 20)           <br>" +
                    "                  where rn >= 1)                        <br>" +
                    " order by order_by desc";
                return sql;
            }else if("mybatis" == type){

                return sql;
            }else{

                return sql;
            }
        }
        /**
         * 分页查询记录数
         */
        function getSelectPageCountSql(type){
            var sql = "--查询分页总记录数<br>";
            if("oracle" == type){
                sql += "select count from " + $("#fieldTableName").val() + " where 1 = 1";
                return sql;
            }else if("mybatis" == type){

                return sql;
            }else{

                return sql;
            }
        }
        /**
         * 修改
         */
        function getUpdateSql(type){
            var sql = "修改<br>";
            if("oracle" == type){
                sql += "update  " + $("#fieldTableName").val() + " set  where 1 = 1";
                return sql;;
            }else if("mybatis" == type){

                return sql;
            }else{

                return sql;
            }
        }
        /**
         * 新增
         */
        function getInsertSql(type){
            var sql = "新增<br>";
            if("oracle" == type){
                sql += "insert into " + $("#fieldTableName").val() + " (";
                for (var i = 0; i < $("#fieldNumber").val(); i++){
                    sql += $("#fieldName_" + i).val() + ",";
                }
                sql = sql.substring(0,sql.length - 1);
                sql += ") values " + "";

                return sql;
            }else if("mybatis" == type){

                return sql;
            }else{

                return sql;
            }
        }

//        insert into BASE_USER (ID, LOGIN_NAME, IS_DELETE, OPERATOR_TYPE, UPDATED_BY, UPDATED_DATE, CREATED_BY, CREATED_DATE)
//        values ('1', '1', 1, 1, '1', to_date('09-01-2017', 'dd-mm-yyyy'), '1', to_date('09-01-2017', 'dd-mm-yyyy'));
//        commit;
//
//        update BASE_USER set LOGIN_NAME='2', IS_DELETE='2', OPERATOR_TYPE='2', UPDATED_BY='2', UPDATED_DATE=sysdate, CREATED_BY='2', CREATED_DATE=sysdate where ID = '1';
        /**
         * 删除
         */
        function getDeleteSql(type){
            var sql = "删除<br>";
            if("oracle" == type){

                return sql;
            }else if("mybatis" == type){

                return sql;
            }else{

                return sql;
            }
        }

    </script>
</head>
</head>
<body>
<input type="hidden" class="form-control" id="fieldTableName">
<input type="hidden" class="form-control" id="fieldNumber">
<form class="form-horizontal" role="form">
    <div class="form-group form-group-lg">
        <label for="driver" class="col-sm-1 control-label">driver</label>
        <div class="col-sm-3">
            <input type="text" class="form-control" id="driver" placeholder="oracle.jdbc.driver.OracleDriver">
        </div>
        <label for="url" class="col-sm-1 control-label">url</label>
        <div class="col-sm-3">
            <input type="text" class="form-control" id="url" placeholder="jdbc:oracle:thin:@localhost:1521:orcl">
        </div>
    </div>
    <div class="form-group">
        <label for="username" class="col-sm-1 control-label">username</label>
        <div class="col-sm-3">
            <input type="text" class="form-control" id="username" placeholder="Password">
        </div>
        <label for="password" class="col-sm-1 control-label">password</label>
        <div class="col-sm-3">
            <input type="text" class="form-control" id="password" placeholder="password">
        </div>
    </div>
    <div class="form-group">
        <div class="col-sm-1">
            &nbsp;
        </div>
        <div class="col-sm-2">
            <button type="button" class="btn btn-primary" onclick="saveCookie()">保存到cookie</button>
            <button type="button" class="btn btn-default" onclick="testConnectionDatabase()">测试连接</button>
        </div>
        <div class="col-sm-2">
            <input type="text" class="form-control" id="tableName" placeholder="输入要查询的表格名称">
        </div>
        <div class="col-sm-4">
            <button type="button" class="btn btn-primary" onclick="searchTable()">查询表格</button>
            <button type="button" id="btnCreatedOracleSqlId" class="btn btn-default" onclick="createdOracleSql()">生成数据库SQL</button>
            <button type="button" id="btnCreatedMybatisSqlId" class="btn btn-default" onclick="createdMybatisSql()">生成Mybatis脚本</button>
        </div>
    </div>
    <div class="col-sm-9">
        <table id="showTableContentId" class="table table-striped">
            <thead>
                <tr>
                    <th style="text-align:center;">#</th>
                    <th style="text-align:center;">字段名称</th>
                    <th style="text-align:center;">字段类型</th>
                    <th style="text-align:center;">说明</th>
                    <th style="text-align:center;">是否主键</th>
                </tr>
            </thead>
            <tbody>
                <tr id="trSearchTableId"></tr>
            </tbody>
        </table>
    </div>
    <div class="form-group col-sm-9">
        <label class="col-sm-1 control-label">返回结果</label>
        <div class="col-sm-10" id="resultSql"></div>
    </div>
</form>
</body>
</html>
